<?php
class ModelMartCrmCrmcustomer extends Model {
    public function getcustomer($data = array()) {
		if ($data) {
			//$sql = "SELECT * FROM " . DB_PREFIX . "crm_customer";
			$sql = "SELECT c.*,e.email_address FROM " . DB_PREFIX . "crm_customer c, email_addresses e,email_addr_bean_rel r";
                        $sql .= " WHERE 1"; 
                        $sql.=" and c.id=r.bean_id and r.email_address_id= e.id";
                        if(isset($data['day'])&& !is_null($data['day'])){
                            $sql .=  " AND date_format(birthday,'%d')='".$this->db->escape($data['day'])."'";
                        }
                        if(isset($data['month'])&& !is_null($data['month'])){
                            $sql .= " AND date_format(birthday,'%m')='".$this->db->escape($data['month'])."'";
                        }
                        if(isset($data['year']) && !is_null($data['year'])){
                            $sql .= " AND date_format(birthday,'%Y')='".$this->db->escape($data['year'])."'";
                        }
                        if(isset($data['email']) && !is_null($data['email'])){
                            $sql .=  " AND LCASE(email_address) LIKE '" . $this->db->escape(utf8_strtolower($data['email'])) . "%'";
                        }
			if (!empty($data['last_name'])) {
				$sql .= " AND LCASE(last_name) LIKE '" . $this->db->escape(utf8_strtolower($data['last_name'])) . "%'";
			}
                        
			if (!empty($data['first_name'])) {
				$sql .= " AND LCASE(first_name) LIKE '" . $this->db->escape(utf8_strtolower($data['first_name'])) . "%'";
			}
			
			if (!empty($data['phonemobile'])) {
				$sql .= " AND phone_mobile LIKE '" . $this->db->escape($data['phonemobile']) . "%'";
			}
                        if (!empty($data['birthday'])) {
				$sql .= " AND birthday LIKE '" . $this->db->escape($data['birthday']) . "%'";
			}
			if (!empty($data['district'])) {
				$sql .= " AND primary_address_street LIKE '" . $this->db->escape($data['district']) . "%'";
			}
                        if (!empty($data['city'])) {
				$sql .= " AND primary_address_city LIKE '" . $this->db->escape($data['city']) . "%'";
			}
			if (isset($data['point']) && !is_null($data['point'])) {
				$sql .= " AND points = '" . $this->db->escape($data['point']) . "'";
			}								
			
			if (isset($data['sort'])) {
				$sql .= " ORDER BY " . $data['sort'];	
			} else {
				$sql .= " ORDER BY last_name";	
			}
			
			if (isset($data['order']) && ($data['order'] == 'DESC')) {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}
		
			if (isset($data['start']) || isset($data['limit'])) {
				if ($data['start'] < 0) {
					$data['start'] = 0;
				}				

				if ($data['limit'] < 1) {
					$data['limit'] = 20;
				}	
			
				$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
			}	
			
			$query = $this->db->query($sql);
		
			return $query->rows;
		}
	}
   public function getnamecustomer($id) {
       
      $sql = " select c.*, e.email_address from  crm_customer c, email_addresses e,email_addr_bean_rel r where c.id='$id' and c.id=r.bean_id and r.email_address_id= e.id";
//       $sql = "SELECT * FROM " . "crm_customer";
//      
//				$sql .= " WHERE id = '$id'";
//			
       $query = $this->db->query($sql);		
       return $query->rows;
   }
   public function editCustomer($id_customer,$data) {
		$this->db->query("UPDATE " . "crm_customer SET phone_mobile = '" . $this->db->escape($data['telephone']) . "', first_name = '" . $this->db->escape($data['firstname']) . "', last_name = '" . $this->db->escape($data['lastname']) . "', alt_address_street = '" . $this->db->escape($data['address1']) . "', primary_address_street = '" . $this->db->escape($data['address2']) . "', primary_address_city = '" . $this->db->escape($data['city']) . "',primary_address_country = '" . $this->db->escape($data['country']) . "' WHERE id  = '" . $id_customer . "'");
                $this->db->query("update email_addresses set email_address= '" . $this->db->escape($data['email']) . "' where id = (select email_address_id from email_addr_bean_rel where bean_id= '$id_customer')");
                
	}
    public function getCustomertotal() {
      	$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "crm_customer");
		
		return $query->row['total'];
	}
    public function getbills($data){
        
            $query = $this->db->query("SELECT crm_customer_bills_crm_customer_c.crm_customer_bills_crm_customercrm_customer_bills_idb FROM " . DB_PREFIX . "crm_customer_bills_crm_customer_c WHERE crm_customer_bills_crm_customer_c.crm_customer_bills_crm_customercrm_customer_ida ='".$data['id']."'" );        
            
            $query1 = $query->rows;
        
            $bill=null;
            for($i=0;$i<count($query1);$i++){
                $sql = "SELECT * FROM " . DB_PREFIX . "crm_customer_bills WHERE id = '".$query1[$i]['crm_customer_bills_crm_customercrm_customer_bills_idb']."'";  
                 if(!empty($data['bill'])){
                   $sql .= " AND name LIKE '". $this->db->escape($data['bill']) ."%'";
                 }
                  if(!empty($data['date_bill'])){
                   $sql .= " AND bill_date LIKE '". $this->db->escape($data['date_bill']) ."%'";
                 }
                $sql1 = $this->db->query($sql);
                $sql2 = $sql1->rows;
                if($sql2 != NULL){
                $bill[$i] = $sql2[0];
                }
            }
            return $bill;
        }
       
        public function getdetailedbills($id){
            $bill= array();
            
            $query = $this->db->query("select crm_customaf9eactions_idb from crm_customer_transactions_crm_customer_bills_c where crm_custom19b9r_bills_ida='$id'" );
            
            $query1 = $query->rows;
            
           
            for($i=0;$i<count($query1);$i++)
            {
               $id = $query1[$i]['crm_customaf9eactions_idb'];
                 $query = $this->db->query("SELECT * FROM `crm_customer_transactions` WHERE id ='$id'" );
                $query3 = $query->rows;  
                
               
                
                  $query = $this->db->query("SELECT name
FROM  `crm_goods` 
WHERE id = ( 
SELECT crm_customer_transactions_crm_goodscrm_goods_ida
FROM crm_customer_transactions_crm_goods_c
WHERE crm_customer_transactions_crm_goodscrm_customer_transactions_idb =  '$id' )" );
                $query4 = $query->rows; 
               // print_r($query4);
                  $bill[$i] = array(
                 'name'     => $query4[0]['name'],
                 'amount'   => $query3[0]['amount'],
                 'quantity' => $query3[0]['quantity'],
                );
              
            
            }
           
            
              
           
            return $bill;  
        }
}
?>

